import time
import datetime
import sqlparse
from sqlparse.sql import Where
from sqlparse.sql import IdentifierList
from sqlparse.sql import Comparison

from app.core.config import large_dml_exec_interval
from app.core.config import dml_batch_tmp_file_path
from app.core.config import backup_fields_separate

from .extra_tables import extract_tables
from .sql_parse_analyze import get_sql_type
from .sql_parse_analyze import get_ddl_table_name
from .sql_execute import mysql_execute
from .sql_execute import mysql_execute_use_pandas
from .sql_execute import mysql_execute_cursor
from .sql_execute import mysql_execute_sql_list
from .sql_execute import mysql_execute_multi_sql

from .dbpt import call_mysql_backup
from .sql_parse_analyze import get_columns_name


def get_next_not_white_space_token(sql_tokens, i):
    while i < len(sql_tokens):
        if sql_tokens[i].is_whitespace is False:
            return sql_tokens[i], i
        i += 1


def get_update_set_column(sql_tokens):
    # for token in sql_tokens:
    for i in range(len(sql_tokens)):
        token = sql_tokens[i]
        if token.value.upper() == 'SET' and token.is_keyword is True:
            # if isinstance(token, IdentifierList):
            update_column, low_index = get_next_not_white_space_token(sql_tokens, i + 1)
            return update_column.value


def get_where_condition(sql_tokens):
    for token in sql_tokens:
        if isinstance(token, Where):
            return token.value


def mysql_data_backup(backup_sql, mysql_info):
    return call_mysql_backup(backup_sql, mysql_info)


def get_table_primary_key(table_name, mysql_info):
    # show_create_sql = f"show create table {table_name};"
    # return create_table_sql[0].get('Create Table')
    database = mysql_info.get('database')
    get_primary_key_sql = f"select COLUMN_NAME , ORDINAL_POSITION from information_schema.COLUMNS " \
                          f"where TABLE_SCHEMA = '{database}' and TABLE_NAME = '{table_name}' " \
                          f"and COLUMN_KEY = 'PRI' order by ORDINAL_POSITION asc ;"
    pk_value_generator = mysql_execute(execute_sql=get_primary_key_sql, **mysql_info)
    return pk_value_generator


def get_primary_key_value(get_primary_key_value_sql, mysql_info):
    # return mysql_execute(get_primary_key_value_sql, **mysql_info)
    return mysql_execute_use_pandas(get_primary_key_value_sql, **mysql_info)


def dml_large_execute(sql, mysql_info, batch_size=8000):
    now_time = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    table_reference = extract_tables(sql)
    if len(table_reference) > 1:
        return 0, f'语句涉及多表,此处不支持,请改为根据单表更改. '
    table_name, db_name = get_ddl_table_name(sql)
    if db_name is not None:
        return 0, f'不能在语句中指定库名.'
    tmp_file = f'{dml_batch_tmp_file_path}/{table_name}-{now_time}-tmp.txt'
    sql_a = sqlparse.parse(sql)[0]
    where_condition = get_where_condition(sql_a.tokens)
    if where_condition is None:
        return 0, f'语句没有where条件. '

    primary_key = get_table_primary_key(table_name, mysql_info=mysql_info)
    primary_key_list = [pk.get('COLUMN_NAME') for pk in primary_key]
    if len(primary_key) == 0:
        return 0, f'表没有主键'
    # primary_key_in_sql = ','.join(primary_key_list)
    sql_type = get_sql_type(sql)

    primary_key_con = ''
    for i in primary_key_list:
        primary_key_con += f'"{i} = " , {i} ,'

    if sql_type.lower() == 'update':
        set_columns = get_update_set_column(sql_a.tokens)
        select_id_for_concat_sql = f"""select concat("update {table_name} set {set_columns} where " , {primary_key_con}  "; \n") from {table_name} {where_condition} """

    elif sql_type.lower() == 'delete':
        select_id_for_concat_sql = f"""select concat("delete from {table_name} where " , {primary_key_con}  "; \n") from {table_name} {where_condition} """
    else:
        return 0, f'不支持的语句类型'

    columns_name = get_columns_name(table_name=table_name, **mysql_info)
    columns_name_list = [i.get('COLUMN_NAME') for i in columns_name]
    columns_name_str = f" , '{backup_fields_separate}' , ".join(columns_name_list)
    backup_sql = f"select concat({columns_name_str}) from {table_name} {where_condition} "
    print(backup_sql)
    backup_result = mysql_data_backup(backup_sql, mysql_info)
    if backup_result.get('status') != 0:
        return 0, f'执行MySQL备份失败'
    # 生成相关的修改主键语句并写文件
    with open(tmp_file, 'w') as f:
        mg = mysql_execute_cursor(select_id_for_concat_sql, dict_cursor=0, **mysql_info)
        for i in mg:
            f.write(i[0])
            # f.flush()

    # 读取相关文件sql语句并进行操作
    with open(tmp_file) as f:
        i = 0
        sql = ""
        for line in f:
            if i < batch_size:
                sql += line
                i += 1
            else:
                i = 0
                sql += line
                mysql_execute_multi_sql(sql, **mysql_info)
                sql = ""
                time.sleep(large_dml_exec_interval)
        mysql_execute_multi_sql(sql, **mysql_info)
    return 1, f'执行成功'
